SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TWSABA_REPO_Q01]
/*-------------------------------------------------------*/
/*---Empresa              : OFISIS S.A.                --*/
/*---Cliente              : SANTILLANA	             --*/
/*---Sistema              : Caja y Bancos              --*/
/*---M+dulo               : Empresa                    --*/
/*---Programa             : CALCULA SALDO INICIAL	 --*/
/*---Script               : TMSABA_M.SQL	       	 --*/
/*---Nombre SP            : SP_TWSABA_REPO_Q01		 --*/
/*---Desarrollado por     : EVP			       --*/
/*---Fecha Creacion	  : 30/10/2000		       --*/ 
/*---Base Datos           : ORACLE			       --*/
/*---Versi+n              : 8.0                        --*/
/*---Invoca a SP          :                            --*/
/*-------------------------------------------------------*/
/*-------------------------------------------------------*/
/*---Modificado 1 por     : EVP			       --*/
/*---Fecha Modificaci+n   : 23/04/2001			 --*/ 
/*---Detalle Modificaci+n : A SQL			       --*/
/*-------------------------------------------------------*/
/*-------------------------------------------------------*/
/*---Modificado 2 por     : Carlos Marquez E			--*/
/*---Fecha Modificaci+n   : 01/09/2005				--*/ 
/*---Detalle Modificaci+n : manejo de Saldos Iniciales 	--*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Comentarios                                       --*/
/*---                                                  --*/
/*---                                                  --*/
/*-------------------------------------------------------*/

/*--------------------------------------------------------------------- */
/* Drop Proc SP_TWSABA_REPO_Q01			 	 		*/
/* Grant All On SP_TWSABA_REPO_Q01 To Public	 			*/
/* 
			Create Table #TWSABA_REPO( 
				CO_EMPR CHAR(02) NULL,	
				CO_BANC VARCHAR(03) NULL,
				NU_CNTA_BANC VARCHAR(20) NULL,
				AA_BNCO VARCHAR(04) NULL,
				MM_BNCO VARCHAR(02) NULL,
				IM_SALD_INIC Numeric(16,4) NULL) 

			Create Table #TWSABA_REPO_AUXI( 
				CO_EMPR VARCHAR(02) NULL,
				CO_BANC VARCHAR(03) NULL,
				NU_CNTA_BANC VARCHAR(20) NULL,
				IM_SALD_INIC Numeric(16,4) NULL) 

SP_TWSABA_REPO_Q01 '2005-07-31', 'O', 'O' , 'O'			
*/
/*----------------------------------------------------------------------*/
@IDFE_OPER TD_DT_001,
@ISTI_FECH TD_VC_001, /*'O','S'*/
@ISFE_INGR TD_VC_001, /*'O','D'*/
@ISFE_EGRE TD_VC_001  /*'O','E','P','C'*/


As
DECLARE
@CSCO_EMPR	TD_VC_002,
@CSCO_BANC	TD_VC_003,
@CSNU_CNTA_BANC  TD_VC_020,
@VSAA_MINI TD_VC_004,
@VSMM_MINI TD_VC_002,


/*Variables*/
@VNIM_INGR     	NUMERIC(16,4),
@VNIM_EGRE		NUMERIC(16,4),
@VNIM_SALD_INIC	NUMERIC(16,4),
@VNIM_SALD_MINI	NUMERIC(16,4)


DECLARE CU_TMCNTA_BANC_Q01_1 CURSOR FOR 
SELECT CO_EMPR , CO_BANC ,  NU_CNTA_BANC 
FROM TMCNTA_BANC 

BEGIN
DELETE FROM #TWSABA_REPO_AUXI
DELETE FROM #TWSABA_REPO

-- PARA CALCULAR EL SALDO INICIAL DEL PRIMER PERIODO DE LAS CUENTAS
Open CU_TMCNTA_BANC_Q01_1
FETCH CU_TMCNTA_BANC_Q01_1 INTO @CSCO_EMPR , @CSCO_BANC , @CSNU_CNTA_BANC
WHILE (@@FETCH_STATUS = 0)
BEGIN
	
	IF (@@FETCH_STATUS < 0)
	Begin
		RAISERROR 20001 'FIN DE ARCHIVO'
		CLOSE CU_TMCNTA_BANC_Q01_1
		DEALLOCATE CU_TMCNTA_BANC_Q01_1
		RETURN
	End
	
	
	SELECT @VSAA_MINI = MIN( AA_BNCO)
	FROM TMSABA_MENS
	WHERE RTRIM(CO_EMPR) = RTRIM(@CSCO_EMPR)
	AND RTRIM(CO_BANC) = RTRIM(@CSCO_BANC)
	AND RTRIM(NU_CNTA_BANC) = RTRIM(@CSNU_CNTA_BANC)
	
	SELECT @VSMM_MINI = MIN( MM_BNCO)
	FROM TMSABA_MENS
	WHERE RTRIM(CO_EMPR) = RTRIM(@CSCO_EMPR)
	AND RTRIM(CO_BANC) = RTRIM(@CSCO_BANC)
	AND RTRIM(NU_CNTA_BANC) = RTRIM(@CSNU_CNTA_BANC)
	AND RTRIM(AA_BNCO) = RTRIM(@VSAA_MINI)
	
	IF @VSAA_MINI IS NOT NULL AND @VSMM_MINI IS NOT NULL 
	BEGIN 
	
		SELECT @VNIM_SALD_MINI = IM_SALD_INIC
		FROM TMSABA_MENS
		WHERE RTRIM(CO_EMPR) = RTRIM(@CSCO_EMPR)
		
AND RTRIM(CO_BANC) = RTRIM(@CSCO_BANC)
		AND RTRIM(NU_CNTA_BANC) = RTRIM(@CSNU_CNTA_BANC)
		AND RTRIM(AA_BNCO) = RTRIM(@VSAA_MINI)
		AND RTRIM(MM_BNCO) = RTRIM(@VSMM_MINI)
		
		INSERT INTO #TWSABA_REPO_AUXI( CO_EMPR, CO_BANC, NU_CNTA_BANC, IM_SALD_INIC)
		VALUES( @CSCO_EMPR, @CSCO_BANC, @CSNU_CNTA_BANC, @VNIM_SALD_MINI)

	END
	
	FETCH CU_TMCNTA_BANC_Q01_1 INTO @CSCO_EMPR , @CSCO_BANC , @CSNU_CNTA_BANC
END

CLOSE CU_TMCNTA_BANC_Q01_1
DEALLOCATE CU_TMCNTA_BANC_Q01_1

	
IF @ISTI_FECH = 'O' 
BEGIN

	INSERT INTO #TWSABA_REPO ( CO_EMPR, CO_BANC, NU_CNTA_BANC, IM_SALD_INIC )
	SELECT CO_EMPR, CO_BANC, NU_CNTA_BANC, 
	ISNULL(SUM( ISNULL(PATINDEX( TI_MOVI_BANC, 'I')*IM_MOVI, 0) - ISNULL(PATINDEX( TI_MOVI_BANC, 'E')*IM_MOVI,0) ), 0)
	FROM TCMOVI_BANC 
	WHERE FE_OPER < @IDFE_OPER
	AND TI_SITU IN ('ACT', 'EXT') 
	GROUP BY CO_EMPR, CO_BANC, NU_CNTA_BANC
	
END -- FIN @ISTI_FECH = 'O'


	
IF @ISTI_FECH = 'S' 

BEGIN

	-- CURSOR DE TODAS LAS CUENTAS
	DECLARE CU_TMCNTA_BANC_Q01_2 CURSOR FOR 
	SELECT CO_EMPR , CO_BANC ,  NU_CNTA_BANC 
	FROM TMCNTA_BANC

	Open CU_TMCNTA_BANC_Q01_2
	FETCH CU_TMCNTA_BANC_Q01_2 INTO @CSCO_EMPR , @CSCO_BANC , @CSNU_CNTA_BANC
	
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
	
		IF (@@FETCH_STATUS < 0)
		Begin
			RAISERROR 20001 'FIN DE ARCHIVO'
			CLOSE CU_TMCNTA_BANC_Q01_2
			DEALLOCATE CU_TMCNTA_BANC_Q01_2
			RETURN
		End
	
	
		IF @ISFE_INGR = 'D'
		BEGIN 
		
			SELECT @VNIM_INGR = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)
			FROM TCMOVI_BANC 
			WHERE TI_SITU IN ('ACT', 'EXT') 
			AND TI_MOVI_BANC = 'I'
			AND CO_EMPR = @CSCO_EMPR
			AND CO_BANC = @CSCO_BANC
			AND NU_CNTA_BANC = @CSNU_CNTA_BANC 
			AND(  ( FE_DISP < @IDFE_OPER
			AND NU_CHEQ IS NOT NULL) Or
			( FE_OPER < @IDFE_OPER
			AND NU_CHEQ IS NULL ) )
	
	
			IF @ISFE_EGRE = 'O' 
			BEGIN
				SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)
				FROM TCMOVI_BANC 
				WHERE TI_SITU IN ('ACT', 'EXT') 
				AND TI_MOVI_BANC = 'E'
				AND CO_EMPR = @CSCO_EMPR
				AND CO_BANC = @CSCO_BANC
				AND NU_CNTA_BANC = @CSNU_CNTA_BANC 
				AND FE_OPER < @IDFE_OPER
		
			END -- @ISFE_EGRE = 'O' 
		
	
			IF @ISFE_EGRE = 'E' 
			BEGIN
				SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)
				FROM TCMOVI_BANC 
				WHERE TI_SITU IN ('ACT', 'EXT') 
				AND TI_MOVI_BANC = 'E' 
				AND CO_EMPR = @CSCO_EMPR
				AND CO_BANC = @CSCO_BANC
				AND NU_CNTA_BANC = @CSNU_CNTA_BANC 
				AND ( ( FE_ENTR_CHEQ < @IDFE_OPER
				AND CO_TIPO_DOCU IS NULL) OR
				( FE_OPER < @IDFE_OPER
				AND CO_TIPO_DOCU IS NOT NULL) )
			
			END -- @ISFE_EGRE = 'E' 
	
			IF @ISFE_EGRE = 'P' 
			BEGIN
				SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)
				FROM TCMOVI_BANC 
				WHERE TI_SITU IN ('ACT', 'EXT') 
				AND TI_MOVI_BANC = 'E' 
				AND CO_EMPR = @CSCO_EMPR
				AND CO_BANC = @CSCO_BANC
				AND NU_CNTA_BANC = @CSNU_CNTA_BANC 
				AND ( ( FE_PROG_CHEQ < @IDFE_OPER
				AND CO_TIPO_DOCU IS NULL) OR
				( FE_OPER < @IDFE_OPER
				AND CO_TIPO_DOCU IS NOT NULL) )
			END -- @ISFE_EGRE = 'P' 
	
			IF @ISFE_EGRE = 'C' 
			BEGIN
				SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)
				FROM TCMOVI_BANC 
				WHERE TI_SITU IN ('ACT', 'EXT') 
				AND TI_MOVI_BANC = 'E' 
				AND CO_EMPR = @CSCO_EMPR
				AND CO_BANC = @CSCO_BANC
				AND NU_CNTA_BANC = @CSNU_CNTA_BANC 
				AND ( ( FE_CHEQ < @IDFE_OPER
				AND CO_TIPO_DOCU IS NULL) OR
				( FE_OPER < @IDFE_OPER
				AND CO_TIPO_DOCU IS NOT NULL) )
			END -- @ISFE_EGRE = 'C' 
	
			SELECT VNIM_SALD_INIC = ISNULL(@VNIM_INGR,0) - ISNULL(@VNIM_EGRE,0)
	
		END -- @ISFE_INGR = 'D' 
	
		IF @ISFE_INGR = 'O' 
		BEGIN
	
			SELECT @VNIM_INGR = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)
			FROM TCMOVI_BANC 
			WHERE TI_SITU IN ('ACT', 'EXT') 
			AND TI_MOVI_BANC = 'I'
			AND CO_EMPR = @CSCO_EMPR
			AND CO_BANC = @CSCO_BANC
			AND NU_CNTA_BANC = @CSNU_CNTA_BANC 
			AND FE_OPER < @IDFE_OPER
	
			IF @ISFE_EGRE = 'O' 
			BEGIN
				SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)
				FROM TCMOVI_BANC 
				WHERE TI_SITU IN ('ACT', 'EXT') 
				AND TI_MOVI_BANC = 'E'
				AND CO_EMPR = @CSCO_EMPR
				AND CO_BANC = @CSCO_BANC
				AND NU_CNTA_BANC = @CSNU_CNTA_BANC 
				AND FE_OPER < @IDFE_OPER
			END -- @ISFE_EGRE = 'O' 
	
	
			IF @ISFE_EGRE = 'E' 
			BEGIN
				SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)
				FROM TCMOVI_BANC 
				WHERE TI_SITU IN ('ACT', 'EXT') 
				AND TI_MOVI_BANC = 'E' 
				AND CO_EMPR = @CSCO_EMPR
				AND CO_BANC = @CSCO_BANC
				AND NU_CNTA_BANC = @CSNU_CNTA_BANC 
				AND ( ( FE_ENTR_CHEQ < @IDFE_OPER
				AND CO_TIPO_DOCU IS NULL) OR
				( FE_OPER < @IDFE_OPER
				AND CO_TIPO_DOCU IS NOT NULL) )
			END --ISFE_EGRE = 'E' 
	
			IF @ISFE_EGRE = 'P' 
			BEGIN
			SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)
			FROM TCMOVI_BANC 
			WHERE TI_SITU IN ('ACT', 'EXT') 
			AND TI_MOVI_BANC = 'E' 
			AND CO_EMPR = @CSCO_EMPR
			AND CO_BANC = @CSCO_BANC
			AND NU_CNTA_BANC = @CSNU_CNTA_BANC 
			AND ( ( FE_PROG_CHEQ < @IDFE_OPER
			AND CO_TIPO_DOCU IS NULL) OR				( FE_OPER < @IDFE_OPER
			AND CO_TIPO_DOCU IS NOT NULL) )

		END -- @ISFE_EGRE = 'P' 
	
		IF @ISFE_EGRE = 'C' 
		BEGIN
			SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)
			FROM TCMOVI_BANC 
			WHERE TI_SITU IN ('ACT', 'EXT') 
			AND TI_MOVI_BANC = 'E' 
			AND CO_EMPR = @CSCO_EMPR
			AND CO_BANC = @CSCO_BANC
			AND NU_CNTA_BANC = @CSNU_CNTA_BANC 
			AND ( ( FE_CHEQ < @IDFE_OPER
			AND CO_TIPO_DOCU IS NULL) OR
			( FE_OPER < @IDFE_OPER
			AND CO_TIPO_DOCU IS NOT NULL) )
		END -- @ISFE_EGRE = 'C' 
	
		SELECT @VNIM_SALD_INIC = ISNULL(@VNIM_INGR, 0) - ISNULL( @VNIM_EGRE,0)
	
	END -- @ISFE_INGR = 'O' 
	
	INSERT INTO #TWSABA_REPO(CO_EMPR, CO_BANC, NU_CNTA_BANC, IM_SALD_INIC )
	VALUES( @CSCO_EMPR, @CSCO_BANC, @CSNU_CNTA_BANC, @VNIM_SALD_INIC )
	
	
	-- FIN @ISTI_FECH = 'S' 
	
	
	FETCH CU_TMCNTA_BANC_Q01_2 INTO @CSCO_EMPR , @CSCO_BANC , @CSNU_CNTA_BANC
	END
	
	CLOSE CU_TMCNTA_BANC_Q01_2
	DEALLOCATE CU_TMCNTA_BANC_Q01_2

END

-- INSERTO CUENTAS QUE NO HAN TENIDO MOVIMIENTOS, ES DECIR AUN NO ESTAN GRABADAS EN TABLA #TWSABA REPO
INSERT INTO #TWSABA_REPO( CO_EMPR, CO_BANC, NU_CNTA_BANC, IM_SALD_INIC )
SELECT T2.CO_EMPR, T2.CO_BANC, T2.NU_CNTA_BANC, 0
FROM TMCNTA_BANC T2
WHERE NOT EXISTS
(SELECT T1.CO_EMPR FROM #TWSABA_REPO T1
WHERE RTRIM(T1.CO_EMPR) = RTRIM(T2.CO_EMPR)
AND RTRIM(T1.CO_BANC) = RTRIM(T2.CO_BANC) 
AND RTRIM(T1.NU_CNTA_BANC) = RTRIM(T2.NU_CNTA_BANC))

-- ACTUALIZO EL SALDO INICIAL MAS EL SALDO INICIAL DEL PRIMER PERIODO QUE SE ABRIO
UPDATE #TWSABA_REPO 
SET #TWSABA_REPO.IM_SALD_INIC =  ( SELECT ISNULL(T2.IM_SALD_INIC,0) + ISNULL(#TWSABA_REPO.IM_SALD_INIC,0) FROM #TWSABA_REPO_AUXI T2
							  WHERE RTRIM(#TWSABA_REPO.CO_EMPR) = RTRIM(T2.CO_EMPR)
							  AND RTRIM(#TWSABA_REPO.CO_BANC) = RTRIM(T2.CO_BANC)
							  AND RTRIM(#TWSABA_REPO.NU_CNTA_BANC) = RTRIM(T2.NU_CNTA_BANC))
WHERE EXISTS( SELECT T3.CO_EMPR
FROM #TWSABA_REPO_AUXI T3
WHERE #TWSABA_REPO.CO_EMPR = T3.CO_EMPR
AND #TWSABA_REPO.CO_BANC = T3.CO_BANC
AND #TWSABA_REPO.NU_CNTA_BANC = T3.NU_CNTA_BANC )

END -- FIN SP
/*------------------------------ FIN ---------------------------------*/


GO
